[SQL] Tricky -to me!- SQL query. - Mailing list pgsql-sql

Dear All,

Consider the following table:

dev_brecard=> select * from test order by person;
person|fruit
------+---------
lucy  |mandarins
lucy  |tomatoes
lucy  |pears
lucy  |oranges
lucy  |apples
peter |pears
peter |apples
peter |oranges
peter |prunes
robert|figs
robert|dates
stuart|apples
stuart|pears
stuart|prunes
stuart|bananas
stuart|kumquats
(16 rows)

(code for creating and populating table is in a PS at the end of this posting)

You can assume that the table is appropriately normalised and that there is
a composite primary key for it (i.e. each COMBINATION of person and fruit
will appear only once and neither of the fields can be NULL)

How do I select from all person who like 'pears' and 'apples' (in this
case, lucy, peter and stuart) or 'pears', 'apples' and 'oranges' (in this
case, lucy and peter)?

I re-read my SQL books but I am still somewhat stumped.  Things I could
think of for that sort of query:

1) Select all persons who like 'pears'; Select all persons who like
'apples'; Select all persons who like 'oranges'; Calculate the INTERSECTION
of these sets (this sort of operation appears to use the EXISTS operator?)

2) Use nested subselects:

Select person from test where person in (
        Select person from test where fruit='pears' and person in (
                Select person from test where fruit='apples' and person in (
                        Select person from test where fruit='oranges'
                )
        )
)

What way do you suggest????

Also, am I storing this sort of data in to wrong kind of form (should I
somehow denormalise?  if so, how?)?

Could you please cc your answers to: stuart@ludwig.ucl.ac.uk

thanks for any help out there!

regards,

Stuart.

PS.  Code to cut and paste for table:

create table test (person    varchar(25), fruit     varchar(25));
insert into test values ('stuart','apples');
insert into test values ('stuart','pears');
insert into test values ('stuart','bananas');
insert into test values ('stuart','kumquats');
insert into test values ('peter','oranges');
insert into test values ('peter','prunes');
insert into test values ('lucy','mandarins');
insert into test values ('lucy','tomatoes');
insert into test values ('peter','apples');
insert into test values ('lucy','apples');
insert into test values ('peter','pears');
insert into test values ('lucy','pears');
insert into test values ('lucy','oranges');
insert into test values ('stuart','prunes');
insert into test values ('robert','figs');
insert into test values ('robert','dates');

+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+